package com.example.demo.executor.sqlGenerate;

import com.example.demo.DBServer.targetDatabase.DBServerDao;
import com.example.demo.entity.ConditionDetailsEntity;
import com.example.demo.entity.ExecuteConditionEntity;
import com.example.demo.entity.ExecuteInformationEntity;
import com.example.demo.entity.TableColumnEntity;
import com.example.demo.exception.BizErrorCode;
import com.example.demo.exception.BizException;
import com.example.demo.typeEnums.*;
import com.example.demo.utils.TimeUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.StringJoiner;

/**
 * mysql的sql语句生成器
 * @author 又菜又爱浪
 */
@Component
public class MysqlSqlGenerator implements SqlGenerator {

    @Autowired
    private DBServerDao dbServerDao;

    @Value("${database.name}")
    private String databaseName;

    @Override
    public String generateDropTableSql(String tableName){
        String dropSql = "DROP TABLE IF EXISTS `"+ tableName + "` ;";
        return dropSql;
    }

    @Override
    public String generateCreateTableSql(String tableName, String backupTableName){
        String createTableSql = "create table if not exists %s like %s;";
        return String.format(createTableSql,backupTableName,tableName);
    }

    @Override
    public String generateCheckTableExistSql(String tableName){
        String checkSql = "SELECT count(1) FROM information_schema.TABLES " +
                "WHERE table_name ='%s' AND table_schema = '%s'";
        return String.format(checkSql,tableName,databaseName);
    }

    @Override
    public String generateDataArchiveSql(String tableName,
                                         String backupTableName, String column, List<Object> conditions){
        String archiveSql = "insert into %s SELECT * FROM %s ";
        StringBuilder archiveBuilder = new StringBuilder(String.format(archiveSql,backupTableName,tableName));
        if(conditions!=null && conditions.size()>0) {
            StringJoiner joiner = new StringJoiner(",");
            conditions.forEach(str -> joiner.add(str.toString()));
            archiveBuilder.append(" WHERE ").append(" ")
                    .append(column).append(" ")
                    .append(" IN").append(" (").append(joiner).append(")");
        }
        return archiveBuilder.toString();
    }

    @Override
    public String generateDeleteSql(String tableName, String column, List<Object> conditions){
        StringBuilder deleteBuilder = new StringBuilder("delete from " + tableName);
        if(conditions!=null && conditions.size()>0) {
            StringJoiner joiner = new StringJoiner(",");
            conditions.forEach(str -> joiner.add(str.toString()));
            deleteBuilder.append(" WHERE ").append(" ")
                    .append(column).append(" ")
                    .append(" IN").append(" (").append(joiner).append(")");
        }
        return deleteBuilder.toString();
    }

    @Override
    public String generateCountNeedDealSizeSql(String tableName, String condition){
        StringBuilder querySizeSql = new StringBuilder("SELECT COUNT(*) FROM ");
        querySizeSql.append(tableName);
        if(StringUtils.isNotBlank(condition)){
            querySizeSql.append(condition);
        }
        return querySizeSql.toString();
    }

    @Override
    public String generateCountAllDataSizeSql(String tableName){
        return "SELECT COUNT(*) FROM " + tableName;
    }

    @Override
    public String generateQueryColumnInfoSql(String tableName){
        String COLUMN_QUERY_SQL = "Select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, CHARACTER_MAXIMUM_LENGTH " +
                "from INFORMATION_SCHEMA.COLUMNS Where table_name = '%s' AND table_schema = '%s'";
        return String.format(COLUMN_QUERY_SQL,tableName,databaseName);
    }

    @Override
    public String generateExecuteConditionSql(ExecuteInformationEntity informationEntity){
        // 得到数据的保留类型
        ReservedType reserveType = ReservedType.getReservedType(informationEntity.getReserveType(),null);

        StringBuilder sqlBuilder = new StringBuilder();
        String condition =this.generateExecuteConditionSql(informationEntity.getExecuteCondition());
        if(StringUtils.isNotEmpty(condition) || !ReservedType.QUANTITY.equals(reserveType)){
            sqlBuilder.append("WHERE ").append(condition).append(" ");
        }
        if(informationEntity.getReserveSize()==null || informationEntity.getReserveSize()<0){
            throw new BizException(BizErrorCode.RESERVE_SIZE_NOT_MEET_REQUIREMENTS);
        }
        if(StringUtils.isBlank(informationEntity.getReserveColumn())){
            throw new BizException(BizErrorCode.RESERVE_COLUMN_NOT_MEET_REQUIREMENTS);
        }
        if(!ReservedType.QUANTITY.equals(reserveType)) {
            Integer reserveTime = informationEntity.getReserveSize() * reserveType.getValue();
            String timeSql = " TO_SECONDS(NOW()) - TO_DAYS(%s) >= %d ";
            sqlBuilder.append(String.format(timeSql,informationEntity.getReserveColumn(),reserveTime));
        }
        return sqlBuilder.toString();
    }

    @Override
    public String generateExecuteConditionSql(ExecuteConditionEntity executeCondition){
        if(executeCondition == null){
            return "";
        }
        String connectType = ConnectType.getConnectType(executeCondition.getConnectType(),null);
        StringBuilder sqlBuilder = new StringBuilder("");

        List<ConditionDetailsEntity> conditionDetailsList = executeCondition.getConditionDetailsList();
        List<ExecuteConditionEntity> executeConditionList = executeCondition.getExecuteConditionList();
        if(conditionDetailsList!=null && conditionDetailsList.size()>0) {
            StringBuilder builder = new StringBuilder();
            for (int i=0; i<conditionDetailsList.size(); i++) {
                ConditionDetailsEntity details = conditionDetailsList.get(i);
                SymbolType symbolType = SymbolType.getSymbolType(details.getSymbol(), null);
                if (symbolType == null) {
                    throw new RuntimeException("Not support this symbol!");
                }
                String symbol = symbolType.getValue();
                switch (symbol) {
                    case "LIKE":
                        builder.append(details.getColumnName())
                                .append(" ").append(symbol).append(" %")
                                .append(details.getCondition()).append("% ");
                        break;
                    case "IN":
                    case "NOT IN":
                        builder.append(details.getColumnName())
                                .append(" ").append(symbol).append(" ( ");
                        for (String condition : details.getConditions()) {
                            builder.append(condition).append(", ");
                        }
                        builder.deleteCharAt(builder.length() - 1);
                        builder.append(") ");
                        break;
                    default:
                        builder.append(details.getColumnName())
                                .append(" ").append(symbol).append(" ")
                                .append(details.getCondition()).append(" ");
                        break;
                }
                if(i != conditionDetailsList.size()-1) {
                    builder.append(connectType).append(" ");
                }
            }
            if(builder.length()>0){
                sqlBuilder.append("(").append(builder).append(" ");
            }
        }
        if(executeConditionList!=null && executeConditionList.size()>0) {
            for (ExecuteConditionEntity conditionEntity : executeConditionList) {
                sqlBuilder.append(connectType).append(" ");
                sqlBuilder.append(this.generateExecuteConditionSql(conditionEntity));
            }
        }
        if(sqlBuilder.length() > 0) {
            sqlBuilder.append(")");
        }
        return sqlBuilder.toString();
    }

    @Override
    public String generateAllParamQuerySql(String tableName, List<String> columns, String condition) {
        if(columns==null || columns.size()==0){
            return null;
        }
        StringBuilder builder = new StringBuilder(" ");
        builder.append("SELECT ");
        for(String column : columns){
            builder.append(column).append(", ");
        }
        builder.deleteCharAt(builder.length()-2);
        builder.append("FROM ").append(tableName).append(" ");
        // 设置where
        if(StringUtils.isNotBlank(condition)){
            builder.append(condition).append(" ");
        }
        return builder.toString();
    }

    public String generatePartParamQuerySql(String paramQuerySql, String sortColumn, String sortType, Integer limit){
        StringBuilder paramQuerySqlBuilder = new StringBuilder(paramQuerySql);
        if(StringUtils.isNotBlank(sortColumn)){
            sortType = SortType.getExecuteType(sortType).getName();
            paramQuerySqlBuilder.append("ORDER BY ").append(sortColumn).append(" ").append(sortType).append(" ");
        }
        if(null!=limit && limit>0){
            paramQuerySqlBuilder.append("limit " + limit);
        }
        return paramQuerySqlBuilder.toString();
    }


    @Override
    public String getDatabaseName() {
        return DatabaseEnum.MYSQL.getDatabaseName();
    }


    /**
     * 生成备份表的建表sql
     * 没有主键，索引和字段约束
     */
    public String generateCreateNoneConstraintTableSql(String tableName, String backupTableName){
        String originalColumnSql = this.generateQueryColumnInfoSql(tableName);
        List<TableColumnEntity> originalColumnInfos = dbServerDao.getTableColumnInfo(originalColumnSql);
        StringBuilder sqlBuilder = new StringBuilder();

        sqlBuilder.append("CREATE TABLE `").append(backupTableName).append("`").append(" ( ");
        String column = " DEFAULT NULL COMMENT '%s'";
        for(TableColumnEntity info : originalColumnInfos){
            String columnName = info.getColumnName();
            String dataType = info.getDataType();
            String comment = info.getColumnComment();
            Long characterMaximumLength = info.getCharacterMaximumLength();
            sqlBuilder.append(columnName).append(" ").append(dataType);
            if(MysqlTimeEnum.isMysqlTimeEnum(dataType)){
                sqlBuilder.append(" NULL ");
            }
            if(characterMaximumLength!=null && characterMaximumLength!=0){
                sqlBuilder.append("(").append(characterMaximumLength).append(")");
            }
            sqlBuilder.append(String.format(column,comment)).append(",");
        }
        sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        sqlBuilder.append(")");
        String tableComment = tableName+" backup table";
        String tableSqlTail = "ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='%s';";
        sqlBuilder.append(String.format(tableSqlTail,tableComment));
        return sqlBuilder.toString();
    }
}
